<?php
/**
 * Created by kongy@mysoft.com.cn.
 * Date: 2016/1/28
 * Time: 15:06
 */

namespace common\services\common;

use common\entities\ConfigEntity;

class TenantService
{
    /**
     * 获得合同的租户信息列表
     * @param string $contractCode 合同编号
     * @return array|false
     */
    public static function getContractTenantList($contractCode = '')
    {
        $sql = "SELECT
                m.contract_id id,
                m.contract_code,
                c.company_id corp_id,
                c.company_name corp_name,
                p.tenant_id,
                p.tenant_code,
                p.tenant_name,
                p.begin_time rental_start,
                p.end_time rental_end,
                p.is_special_approval,
                p.special_approval_type,
                p.promise_online,
                p.actual_online,
                p.created_on,
                p.created_by,
                p.modified_on,
                p.modified_by
                FROM myscrm_contract_manage m
                inner join (
                    SELECT contract_code,tenant_id,tenant_code,tenant_name,
                    '0' is_special_approval,null promise_online,null actual_online,
                    IFNULL(begin_time,authorize_time) begin_time,
					IFNULL(end_time,DATE_ADD(authorize_time,INTERVAL authorize_period MONTH)) end_time,
					created_on,created_by,modified_on,modified_by,
                    null special_approval_type,authorize_time
                    FROM myscrm_contract_payover
                    UNION ALL
                    SELECT contract_code,'' tenant_id,'' tenant_code,'' tenant_name,
                    '1' is_special_approval,promise_fulfil_time promise_online,actual_fulfil_time actual_online,
                    '' begin_time,'' end_time,created_on,created_by,modified_on,modified_by,
                    case category when 1 then '合同未归档'
                    when 2 then '首期款未回'
                    when 3 then '授权信息与合同信息不一致'
                    when 4 then '合同扫描件未提供'
                    when 5 then '结算款未支付'
                    when 6 then '其他'
                    when 7 then '合同未归档,首期款未回'
                    else '' end special_approval_type,created_on authorize_time
                    FROM myscrm_special_approval_authorize
                ) p on m.contract_code=p.contract_code
                inner join myscrm_contract_company c on m.company_id=c.company_id
                WHERE IFNULL(m.contract_code,'')!='' ";

        if(!empty($contractCode)){
            $sql .= ' AND m.contract_code=:contract_code';
            $params = [':contract_code' => $contractCode];
        }else{
            $params = [];
        }
        $result = ConfigEntity::getDb()->createCommand($sql,$params)->queryAll();

        return $result;
    }

    public static function getContractsList($beginTime, $endTime)
    {
        $sql = "SELECT
                m.contract_id id,
                m.contract_code,
                c.company_id corp_id,
                c.company_name corp_name,
                p.tenant_id,
                p.tenant_code,
                p.tenant_name,
                p.begin_time rental_start,
                p.end_time rental_end,
                p.is_special_approval,
                p.special_approval_type,
                p.promise_online,
                p.actual_online,
                p.created_on,
                p.created_by,
                p.modified_on,
                p.modified_by
                FROM myscrm_contract_manage m
                inner join (
                    SELECT contract_code,tenant_id,tenant_code,tenant_name,
                    '0' is_special_approval,null promise_online,null actual_online,
                    IFNULL(begin_time,authorize_time) begin_time,
					IFNULL(end_time,DATE_ADD(authorize_time,INTERVAL authorize_period MONTH)) end_time,
					created_on,created_by,modified_on,modified_by,
                    null special_approval_type,authorize_time
                    FROM myscrm_contract_payover
                    UNION ALL
                    SELECT contract_code,'' tenant_id,'' tenant_code,'' tenant_name,
                    '1' is_special_approval,promise_fulfil_time promise_online,actual_fulfil_time actual_online,
                    '' begin_time,'' end_time,created_on,created_by,modified_on,modified_by,
                    case category when 1 then '合同未归档'
                    when 2 then '首期款未回'
                    when 3 then '授权信息与合同信息不一致'
                    when 4 then '合同扫描件未提供'
                    when 5 then '结算款未支付'
                    when 6 then '其他'
                    when 7 then '合同未归档,首期款未回'
                    else '' end special_approval_type,created_on authorize_time
                    FROM myscrm_special_approval_authorize
                ) p on m.contract_code=p.contract_code
                left join myscrm_contract_company c on m.company_id=c.company_id
                WHERE IFNULL(m.contract_code,'')!='' ";

        if(!empty($beginTime) && !empty($endTime)){
            $sql .= ' AND p.authorize_time BETWEEN :beginTime AND :endTime';
            $params = [':beginTime' => $beginTime, ':endTime' => $endTime];
        }else{
            $params = [];
        }

        $result = ConfigEntity::getDb()->createCommand($sql,$params)->queryAll();

        return $result;
    }
}